<?php


namespace app\erp\controller;


use app\erp\model\Cartdb;
use app\erp\model\Goodsdb;
use app\erp\model\Huikuandb;
use app\erp\model\Orderdb;
use app\erp\model\Qingkuandb;
use app\erp\model\Qingkuanstatedb;
use app\erp\model\Rukuhuizongdb;
use app\erp\model\Shoppingdb;
use think\Db;
use think\Exception;

class Qingkuan extends Base
{
    protected $cols = array(
        array(
            'field' => 'id',
            'width' => 40,
            'title' => 'ID',
            'type' => 'checkbox',
            'fixed' => 'left'
        ),
        array(
            'fixed' => 'left',
            'width' => 80,
            'title' => '操作',
            'toolbar' => '#qingkuan_tool'
        ),
        array(
            'field' => 'zizhi',
            'width' => 100,
            'title' => '资质状态',
            'templet' => '#zizhiTpl'
        ),
        array(
            'field' => 'out_order_no',
            'width' => 240,
            'title' => '军网订单号'
        ),
        array(
            'field' => 'ruku',
            'width' => 100,
            'title' => '入库状态',
            'templet' => '#rukuTpl'
        ),
        array(
            'field' => 'huikuan',
            'width' => 100,
            'title' => '回款状态',
            'templet' => '#huikuanTpl'
        ),
        array(
            'field' => 'bili_txt',
            'width' => 120,
            'title' => '回款比例',
        ),
        array(
            'field' => 'renkuanrizhi',
            'width' => 160,
            'title' => '认款日志',
        ),
        array(
            'field' => 'pici_state',
            'width' => 100,
            'title' => '请款状态',
            'templet' => '#pici_stateTpl'
        ),
        array(
            'field' => 'pici',
            'width' => 160,
            'title' => '请款批次'
        ),
        array(
            'field' => 'caigou',
            'width' => 120,
            'title' => '采购金额'
        ),
        array(
            'field' => 'qingkuan',
            'width' => 120,
            'title' => '可请款金额'
        ),
        array(
            'field' => 'zhuangtai',
            'width' => 100,
            'title' => '状态',
            'templet' => '#zhuangtaiTpl'
        ),
        array(
            'field' => 'kaipiao',
            'width' => 100,
            'title' => '供开票状态',
            'templet' => '#kaipiaoTpl'
        ),
        array(
            'field' => 'qiantai_txt',
            'width' => 120,
            'title' => '前台状态'
        ),
        array(
            'field' => 'zhifu',
            'width' => 100,
            'title' => '支付状态',
            'templet' => '#zhifuTpl'
        ),
        array(
            'field' => 'gongyingshang',
            'width' => 300,
            'title' => '一级供应商名称'
        ),
    );
    public $post = array();

    public function _initialize()
    {
        parent::_initialize();
        $request = request();
        $param = $request->param();
        $this->post = $param;
    }

    public function index()
    {
        $this->assign('cols', json_encode($this->cols));
        return $this->fetch();
    }

    public function ajaxList()
    {
        $qingkuandb = new Qingkuandb();
        $qingkuandb->param = $this->post;
        $get_data = $qingkuandb->order('id desc')->paginate($this->post['limit']);
        $this->layuiReturn($get_data->items(), $get_data->total());
    }

    /**
     * Title("信息对比")
     */
    public function deffview($out_order_no)
    {
        $rukuhuizongdb = new Rukuhuizongdb();
        $get_huizong = $rukuhuizongdb
            ->field('address,contact,phone,gongyingshang,kouchu,goods_name,goods_code,xinghao,shuiwudalei,kaipiaomingcheng,danwei,shuliang,danjia,caigou')
            ->where('out_order_no', $out_order_no)
            ->select();
        $get_order = Db::name('order')->where('out_order_no', $out_order_no)->field('order_no,shopping_id,supplier_id,supplier_rate')->find();
        $get_supplier = Db::name('member')->where('member_id', $get_order['supplier_id'])->value('supplier_name');
        $shoppingdb = new Shoppingdb();
        $get_shopping = $shoppingdb->find($get_order['shopping_id']);
        $cartdb = new Cartdb();
        $get_cart = $cartdb->where('order_no', $get_order['order_no'])->select();
        $old_arr = [];
        foreach ($get_cart as $key => $val) {
            $goodsdb = new Goodsdb();
            $get_goods = $goodsdb->where('goods_sku', $val['goods_sku'])->field('goods_name,goods_model,goods_unit,upc')->find();
            //$caigou_price = Db::name('sale_price')->where('goods_sku', $val['goods_sku'])->value('caigoujia');
            $temp = [];
            $temp['address'] = $get_shopping['province'] . '·' . $get_shopping['city'] . '·' . $get_shopping['area'] . $get_shopping['street'];
            $temp['contact'] = $get_shopping['contacts'];
            $temp['phone'] = $get_shopping['phone'];
            $temp['gongyingshang'] = $get_supplier;
            $temp['kouchu'] = $get_order['supplier_rate'];
            $temp['goods_name'] = $get_goods['goods_name'];
            $temp['goods_code'] = $get_goods['upc'];
            $temp['xinghao'] = $get_goods['goods_model'];
            $temp['shuiwudalei'] = $val['product_area'];
            $temp['kaipiaomingcheng'] = $val['product_firm'];
            $temp['danwei'] = $get_goods['goods_unit'];
            $temp['shuliang'] = $val['goods_num'];
            $temp['danjia'] = $val['goods_sale_price'];
            $temp['caigou'] = number_format($val['goods_sale_price'] * $get_order['supplier_rate'] / 100, 2, '.', '');
            array_push($old_arr, $temp);
        }
        $this->assign('new_arr', $get_huizong);
        $this->assign('old_arr', $old_arr);
        return $this->fetch();
    }

    /**
     * Title("导入订单入库")
     */
    public function rukuzongbiao($file)
    {
        set_time_limit(3600);
        ini_set('memory_limit', '256M');
        $file = str_replace('http', '', str_replace('https', '', $file));
        $file = str_replace('://' . $_SERVER ['HTTP_HOST'], '.', $file);
        vendor("PHPExcel.PHPExcel");
        Vendor('PHPExcel.Classes.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.Classes.PHPExcel.Writer.Excel2007');
        $phpExcel = new \PHPExcel ();
        $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        if ($extension == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        } else if ($extension == 'xls') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        $objPHPExcel = $objReader->load($file);
        $sheet = $objPHPExcel->getSheet(0);
        $allRow = $sheet->getHighestRow();
        $allCol = $sheet->getHighestColumn();
        $phpexcel_shared_date = new \PHPExcel_Shared_Date ();
        if ($allCol != 'Z') {
            $this->layuiReturn('error', '文件不符合模板', -1);
        }
        $save_arr = [];
        $update_arr = [];
        $order_no_arr = [];
        $rukuhuizongDb = new Rukuhuizongdb();
        for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) { // 循环读取每个单元格的内容。注意行从1开始，列从A开始
            if ($sheet->getCellByColumnAndRow(0, $rowIndex)->getValue()) {
                $temp = [];
                $qun_date = $sheet->getCellByColumnAndRow(0, $rowIndex)->getValue();
                try {
                    $qun_date_str = date('Y/m/d', $phpexcel_shared_date->ExcelToPHP($qun_date));
                    $temp['qun_date'] = $qun_date_str;
                } catch (Exception $e) {
                    $temp['qun_date'] = $qun_date;
                }
                $temp['leixing'] = $sheet->getCellByColumnAndRow(1, $rowIndex)->getValue();
                $temp['out_order_no'] = (string)$sheet->getCellByColumnAndRow(2, $rowIndex)->getValue();
                $temp['fahuo_date'] = (string)$sheet->getCellByColumnAndRow(3, $rowIndex)->getValue();
                $temp['shouhuo_date'] = (string)$sheet->getCellByColumnAndRow(4, $rowIndex)->getValue();
                $temp['address'] = $sheet->getCellByColumnAndRow(5, $rowIndex)->getValue();
                $temp['contact'] = $sheet->getCellByColumnAndRow(6, $rowIndex)->getValue();
                $temp['phone'] = (string)$sheet->getCellByColumnAndRow(7, $rowIndex)->getValue();
                $temp['gongyingshang'] = $sheet->getCellByColumnAndRow(8, $rowIndex)->getValue();
                $temp['kouchu'] = $sheet->getCellByColumnAndRow(9, $rowIndex)->getValue();
                $temp['goods_name'] = $sheet->getCellByColumnAndRow(10, $rowIndex)->getValue();
                $temp['goods_code'] = $sheet->getCellByColumnAndRow(11, $rowIndex)->getValue();
                $temp['dxt_name'] = $sheet->getCellByColumnAndRow(12, $rowIndex)->getValue();
                $temp['xinghao'] = $sheet->getCellByColumnAndRow(13, $rowIndex)->getValue();
                $temp['shuiwudalei'] = $sheet->getCellByColumnAndRow(14, $rowIndex)->getValue();
                $temp['kaipiaomingcheng'] = $sheet->getCellByColumnAndRow(15, $rowIndex)->getValue();
                $temp['guigexinghao'] = $sheet->getCellByColumnAndRow(16, $rowIndex)->getValue();
                $temp['danwei'] = $sheet->getCellByColumnAndRow(17, $rowIndex)->getValue();
                $temp['shuliang'] = $sheet->getCellByColumnAndRow(18, $rowIndex)->getValue();
                $temp['danjia'] = $sheet->getCellByColumnAndRow(19, $rowIndex)->getValue();
                $temp['caigou'] = $sheet->getCellByColumnAndRow(22, $rowIndex)->getValue();
                $temp['beizhu'] = $sheet->getCellByColumnAndRow(24, $rowIndex)->getValue();
                $chk = $rukuhuizongDb->where('out_order_no', $temp['out_order_no'])->where('goods_code', $temp['goods_code'])->find();
                if ($chk) {
                    $temp['id'] = $chk['id'];
                    array_push($update_arr, $temp);
                } else {
                    array_push($save_arr, $temp);
                }
                array_push($order_no_arr, $temp['out_order_no']);
            }
        }
        if (count($save_arr)) {
            $rukuhuizongDb->saveAll($save_arr);
        }
        if (count($update_arr)) {
            $rukuhuizongDb->saveAll($update_arr);
        }
        $this->calcQingkuan($order_no_arr);
        $this->layuiReturn('', 1, '导入成功', 1);
    }

    /**
     * Title("导入回款比例")
     */
    public function huikuanbili($file)
    {
        set_time_limit(3600);
        ini_set('memory_limit', '256M');
        $file = str_replace('http', '', str_replace('https', '', $file));
        $file = str_replace('://' . $_SERVER ['HTTP_HOST'], '.', $file);
        vendor("PHPExcel.PHPExcel");
        Vendor('PHPExcel.Classes.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.Classes.PHPExcel.Writer.Excel2007');
        $phpExcel = new \PHPExcel ();
        $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        if ($extension == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        } else if ($extension == 'xls') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        $objPHPExcel = $objReader->load($file);
        $sheet = $objPHPExcel->getSheet(0);
        $allRow = $sheet->getHighestRow();
        $allCol = $sheet->getHighestColumn();
        $phpexcel_shared_date = new \PHPExcel_Shared_Date ();
        if ($allCol != 'J') {
            $this->layuiReturn('error', '文件不符合模板', -1);
        }
        $huikuanDb = new Huikuandb();
        $save_arr = [];
        $update_arr = [];
        $order_no_arr = [];
        for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) { // 循环读取每个单元格的内容。注意行从1开始，列从A开始
            if ($sheet->getCellByColumnAndRow(0, $rowIndex)->getValue()) {
                $temp = [];
                $temp['out_order_no'] = (string)$sheet->getCellByColumnAndRow(0, $rowIndex)->getValue();
                $temp['bili'] = $sheet->getCellByColumnAndRow(1, $rowIndex)->getValue();
                $temp['money'] = $sheet->getCellByColumnAndRow(2, $rowIndex)->getValue();
                $temp['gongyingshang'] = $sheet->getCellByColumnAndRow(3, $rowIndex)->getValue();
                $temp['sale_money'] = $sheet->getCellByColumnAndRow(4, $rowIndex)->getValue();
                $temp['callet_money'] = $sheet->getCellByColumnAndRow(5, $rowIndex)->getValue();
                $temp['return_code'] = (string)$sheet->getCellByColumnAndRow(6, $rowIndex)->getValue();
                $renkuan_date = $sheet->getCellByColumnAndRow(7, $rowIndex)->getValue();
                try {
                    $renkuan_date_str = date('Y/m/d', $phpexcel_shared_date->ExcelToPHP($renkuan_date));
                    $temp['renkuan_date'] = $renkuan_date_str;
                } catch (Exception $e) {
                    $temp['renkuan_date'] = $renkuan_date;
                }
                $dakuan_date = $sheet->getCellByColumnAndRow(8, $rowIndex)->getValue();
                try {
                    $dakuan_date_str = date('Y/m/d', $phpexcel_shared_date->ExcelToPHP($dakuan_date));
                    $temp['dakuan_date'] = $dakuan_date_str;
                } catch (Exception $e) {
                    $temp['dakuan_date'] = $dakuan_date;
                }
                $temp['huikuan_flag'] = $temp['return_code'] ? 1 : 2;
                $chk = $huikuanDb->where('out_order_no', $temp['out_order_no'])->find();
                if ($chk) {
                    array_push($update_arr, $temp);
                } else {
                    array_push($save_arr, $temp);
                }
                array_push($order_no_arr, $temp['out_order_no']);
            }
        }

        if (count($save_arr)) {
            $huikuanDb->saveAll($save_arr);
        }
        if (count($update_arr)) {
            $huikuanDb->saveAll($update_arr);
        }
        $this->calcQingkuan($order_no_arr);
        $this->layuiReturn('', 1, '导入成功', 1);
    }

    /**
     * Title("导入请款状态")
     *
     */
    public function qiangkuanstate($file)
    {
        set_time_limit(3600);
        ini_set('memory_limit', '256M');
        $file = str_replace('http', '', str_replace('https', '', $file));
        $file = str_replace('://' . $_SERVER ['HTTP_HOST'], '.', $file);
        vendor("PHPExcel.PHPExcel");
        Vendor('PHPExcel.Classes.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.Classes.PHPExcel.Writer.Excel2007');
        $phpExcel = new \PHPExcel ();
        $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        if ($extension == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        } else if ($extension == 'xls') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        $objPHPExcel = $objReader->load($file);
        $sheet = $objPHPExcel->getSheet(0);
        $allRow = $sheet->getHighestRow();
        $allCol = $sheet->getHighestColumn();
        $phpexcel_shared_date = new \PHPExcel_Shared_Date ();
        /*if ($allCol != 'C') {
            $this->layuiReturn('error', '文件不符合模板', -1);
        }*/
        $qingkuanstatedb = new Qingkuanstatedb();
        $save_arr = [];
        $update_arr = [];
        $order_no_arr = [];
        for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) { // 循环读取每个单元格的内容。注意行从1开始，列从A开始
            if ($sheet->getCellByColumnAndRow(0, $rowIndex)->getValue()) {
                $temp = [];
                $temp['out_order_no'] = (string)$sheet->getCellByColumnAndRow(0, $rowIndex)->getValue();
                $get_state = $sheet->getCellByColumnAndRow(2, $rowIndex)->getValue();
                switch ($get_state) {
                    case '已请款':
                        $temp['state'] = 1;
                        break;
                    case '部分请款':
                        $temp['state'] = 3;
                        break;
                    case '保金请款':
                        $temp['state'] = 4;
                        break;
                    case '待重新请款':
                        $temp['state'] = 5;
                        break;
                    default:
                        $temp['state'] = 2;
                        break;
                }
                $pici = $sheet->getCellByColumnAndRow(3, $rowIndex)->getValue();

                try {
                    $pici_date_str = date('Y/m/d', $phpexcel_shared_date->ExcelToPHP($pici));
                    $temp['pici'] = $pici_date_str;
                } catch (Exception $e) {
                    $temp['pici'] = $pici;
                }


                $ruku_state = $sheet->getCellByColumnAndRow(1, $rowIndex)->getValue();
                try {
                    $ruku_state_str = date('Y/m/d', $phpexcel_shared_date->ExcelToPHP($ruku_state));
                    $temp['ruku_state'] = 1;
                } catch (Exception $e) {
                    $temp['ruku_state'] = 0;
                }

                // 同步订单状态
                $orderdb = new Orderdb();
                $orderdb->where('out_order_no', $temp['out_order_no'])
                    ->update([
                        'ruku' => $temp['ruku_state'],
                        'ruku_time' => strtotime(date('Y-m-d H:i:s', $phpexcel_shared_date->ExcelToPHP($pici)))
                    ]);


                $chk = $qingkuanstatedb->where('out_order_no', $temp['out_order_no'])->find();
                if ($chk) {
                    array_push($update_arr, $temp);
                } else {
                    array_push($save_arr, $temp);
                }
                array_push($order_no_arr, $temp['out_order_no']);
            }
        }

        if (count($save_arr)) {
            $qingkuanstatedb->saveAll($save_arr);
        }
        if (count($update_arr)) {
            $qingkuanstatedb->saveAll($update_arr);
        }
        $this->calcQingkuan($order_no_arr);
        $this->layuiReturn('', 1, '导入成功', 1);
    }

    public function getZhekouMoneyAttr($order_no, $supplier_rate)
    {
        if ($supplier_rate) {
            $zhekou_money = 0;
            $cartdb = new Cartdb ();
            $get_cart = $cartdb->useGlobalScope(false)->where('order_no', $order_no)->select();
            foreach ($get_cart as $s => $a) {
                $zhekou_money += round($a ['goods_price'] * $supplier_rate) / 100 * $a ['goods_num'];
            }
            $supplier_rate_money = number_format($zhekou_money, 2, '.', '');

        } else {
            $supplier_rate_money = 0;
        }
        return $supplier_rate_money;
    }

    /**
     * Title("计算请款状态")
     */
    public function calcQingkuan($order_no_arr)
    {
        $order_no_arr = array_unique($order_no_arr);
        $save_data = [];
        $update_data = [];
        $orderdb = new Orderdb();
        $huikuandb = new Huikuandb();
        $rukuhuizongdb = new Rukuhuizongdb();
        $qingkuandb = new Qingkuandb();
        $qingkuanstatedb = new Qingkuanstatedb();

        foreach ($order_no_arr as $key => $val) {
            $get_order = $orderdb->field('ruku,caigou_invoice_pz,jun_order_state,ghs_pay,order_id,supplier_rate,order_no')->where('out_order_no', $val)->find();
            $get_huikuan = $huikuandb->field('out_order_no,bili,gongyingshang,sale_money,callet_money,huikuan_flag,renkuan_date')->where('out_order_no', $val)->find();
            $get_ruku = $rukuhuizongdb->field('out_order_no,sum(caigou*shuliang) all_jine')->where('out_order_no', $val)->group('out_order_no')->find();
            $get_qingkuanstate = $qingkuanstatedb->field('out_order_no,state pici_state,pici,ruku_state')->where('out_order_no', $val)->find();

            $temp = [];
            $temp['zizhi'] = 0;
            $temp['out_order_no'] = $val;
            $temp['ruku'] = $get_qingkuanstate['ruku_state'];
            $temp['bili'] = $get_huikuan['bili'];
            $temp['renkuanrizhi'] = $get_huikuan['renkuan_date'];
            $temp['huikuan'] = $get_huikuan['huikuan_flag'];
            $temp['pici_state'] = $get_qingkuanstate['pici_state'];// 未给表格
            $temp['pici'] = $get_qingkuanstate['pici'];//未给表格
            $temp['caigou'] = $this->getZhekouMoneyAttr($get_order['order_no'], $get_order['supplier_rate']);
            $temp['qingkuan'] = $get_ruku['all_jine'];
            $temp['zhuangtai'] = number_format($temp['caigou'], 2, '.', '') == number_format($temp['qingkuan'], 2, '.', '') ? 1 : 0;
            $temp['kaipiao'] = $get_order['caigou_invoice_pz'] ? 1 : 0;
            $temp['qiantai'] = $get_order['jun_order_state'];
            $temp['zhifu'] = $get_order['ghs_pay'] == 1 ? 1 : 0;
            $temp['gongyingshang'] = $get_huikuan['gongyingshang'];
            // 校验资质
            if ($temp['ruku'] == 1 && $temp['huikuan'] == 1 && $temp['bili'] == 1 && $temp['renkuanrizhi'] != '' &&
                $temp['pici_state'] == 1 && $temp['kaipiao'] == 1 && $temp['zhuangtai'] != 4 &&
                $temp['zhifu'] == 0 && $temp['zhuangtai'] == 1 && $temp['gongyingshang'] != ''
            ) {
                $temp['zizhi'] = 1;
            }
            if ($temp['ruku'] == 1 && $temp['huikuan'] == 1 && $temp['bili'] == 1 && $temp['renkuanrizhi'] != '' &&
                $temp['pici_state'] == 1 && $temp['kaipiao'] == 1 && $temp['zhuangtai'] != 4 &&
                $temp['zhifu'] == 1 && $temp['zhuangtai'] == 1 && $temp['gongyingshang'] != ''
            ) {
                $temp['zizhi'] = 2;
            }
            $get_qingkuan = $qingkuandb->where('out_order_no', $val)->find();
            if ($get_qingkuan) {
                $temp['id'] = $get_qingkuan['id'];
                array_push($update_data, $temp);
            } else {
                array_push($save_data, $temp);
            }
        }
        if (count($save_data)) {
            $qingkuandb->saveAll($save_data);
        }
        if (count($update_data)) {
            $qingkuandb->saveAll($update_data);
        }
    }

    /**
     * Title("导出本次查询")
     */
    public function exportxsl(){
        set_time_limit(0);
        ini_set('memory_limit', '12000M');
        $qingkuandb = new Qingkuandb();
        $qingkuandb->param = $this->post;
        $get_sql = $qingkuandb->fetchSql(true)->column('out_order_no');
        $rukuhuizongdb = new Rukuhuizongdb();
        $get_data = $rukuhuizongdb
            //->fetchSql(true)
            ->whereExp('out_order_no','IN('.$get_sql.')')
            ->field('out_order_no,gongyingshang,dxt_name,shuliang,danjia,caigou,beizhu')
            ->order('out_order_no asc')
            ->select();
        vendor("PHPExcel.PHPExcel");
        $phpExcel = new \PHPExcel ();
        ob_end_clean();
        $excel_name = '请款' . date('Ymdhi') . '.' . rand(1000, 9999) . '.xls';
        $obj_write = new \PHPExcel_Writer_Excel5 ($phpExcel);
        $obj_act_sheet = $phpExcel->getActiveSheet();
        $header_data = array(
            '订单编号',
            '供应商名称',
            '迪信通系统名称(D)',
            '数量',
            '销售金额',
            '采购单价',
            '采购金额',
            '备注'
        );
        $this->setExcelHeader($obj_act_sheet, $phpExcel, $header_data);
        $excel_data = array();
        foreach ($get_data as $key => $val) {
            $temp = [];
            $temp[0] = $val['out_order_no'];
            $temp[1] = $val['gongyingshang'];
            $temp[2] = $val['dxt_name'];
            $temp[3] = $val['shuliang'];
            $temp[4] = $val['danjia'];
            $temp[5] = $val['caigou'];
            $temp[6] = $val['caigou'] * $val['shuliang'];
            $temp[7] = $val['beizhu'];
            array_push($excel_data, $temp);
        }
        $vAlign = new \PHPExcel_Style_Alignment ();
        $this->setExcelBody($obj_act_sheet, $phpExcel, $excel_data, count($header_data), $vAlign::VERTICAL_CENTER);

        $obj_write->save('./uploads/Excel/' . $excel_name);
        $all_path = $excel_name;
        $this->layuiReturn(url('Order/downExcel', array(
            'excel_name' => $all_path
        )), 1, '导出成功', 1);
    }

    protected function setExcelHeader($obj_act_sheet, $phpExcel, $data)
    {
        $start = 65;
        for ($i = 0; $i < count($data); $i++) {
            if ($i >= 52) {
                $cell_value = chr(66) . chr($start + ($i - 52)) . '1';
                $cell_name = chr(66) . chr($start + ($i - 52));
            } elseif ($i >= 26) {
                $cell_value = chr(65) . chr($start + ($i - 26)) . '1';
                $cell_name = chr(65) . chr($start + ($i - 26));
            } else {
                $cell_value = chr($start + $i) . '1';
                $cell_name = chr($start + $i);
            }
            $obj_act_sheet->setCellValue($cell_value, $data [$i]);
            $phpExcel->getActiveSheet()->getColumnDimension($cell_name)->setWidth(20);
        }
    }

    protected function setExcelBody($obj_act_sheet, $phpExcel, $data, $count, $valign)
    {
        $start = 65;
        for ($i = 0; $i < $count; $i++) {
            if ($i >= 52) {
                $cell_value = chr(66) . chr($start + ($i - 52));
            } elseif ($i >= 26) {
                $cell_value = chr(65) . chr($start + ($i - 26));
            } else {
                $cell_value = chr($start + $i);
            }
            $j = 2;
            $end_merge = 2;
            foreach ($data as $key => $val) {
                $obj_act_sheet->setCellValue($cell_value . $j, str_replace('_diff', '', str_replace('_add', '', $val [$i])));
                // 合并单元格
                $phpExcel->getActiveSheet()->getStyle($cell_value . $j)->getAlignment()->setVertical($valign);
                $j++;
            }
        }
        // 合并单元格
        $temp = '';
        $ceil_num = 0;
        $start_num = 2; // 第二行是正本数据
        for ($x = 0; $x < count($data); $x++) {
            if ($data [$x] [0] != $temp) {
                $temp = $data [$x] [0];
                if ($ceil_num > 1) {
                    for ($y = 0; $y < 2; $y++) {
                        $cell_value = chr($start + $y);
                        if ($y >= 26) {
                            $cell_value = 'A' . chr($start + $y - 26);
                        }
                        $phpExcel->getActiveSheet()->mergeCells($cell_value . $start_num . ':' . $cell_value . ($start_num + $ceil_num - 1));
                    }
                }
                $start_num += $ceil_num;
                $ceil_num = 1;
            } else {
                $ceil_num++;
            }
        }
        if ($ceil_num > 1) {
            for ($y = 0; $y < 2; $y++) {
                $cell_value = chr($start + $y);
                if ($y >= 26) {
                    $cell_value = 'A' . chr($start + $y - 26);
                }
                $phpExcel->getActiveSheet()->mergeCells($cell_value . $start_num . ':' . $cell_value . ($start_num + $ceil_num - 1));
            }
        }
    }
}